﻿ALTER PROCEDURE [dbo].[medianamik_LoadChildNodes] 
	@TableName varchar(250), 
	@NodeId uniqueidentifier,
	@CultureName varchar(5),
	@Generation int,
	@MaxDepth int = 0,
	@IsActive int = -1,
	@InclusiveChar varchar(2) = '_%'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Declare @IsActiveSql varchar(100)
	IF @IsActive <> -1
		BEGIN
			set @IsActiveSql = 'AND [IsActive] = ' + CAST(@IsActive AS VARCHAR)
		END
    -- Insert statements for procedure here
	IF @MaxDepth = 0
		BEGIN
			exec('SELECT * 
			FROM ' + @TableName + '
			WHERE [Path] LIKE (SELECT [Path]
				   FROM medianamik_node
				   WHERE [NodeId] = ''' + @NodeId + ''')
				   + ''' + @InclusiveChar + '''
			AND [CultureName] = ''' + @CultureName + '''
			AND [Generation] = ' + @Generation + 
			@IsActiveSql +
			'ORDER BY [Depth] ASC, [Position] ASC, [CreatedOn] Desc')
		END
	ELSE
		BEGIN
			exec('SELECT * 
			FROM ' + @TableName + '
			WHERE [Path] LIKE (SELECT [Path]
			   FROM medianamik_node
			   WHERE [NodeId] = ''' + @NodeId + ''')
			   + ''' + @InclusiveChar + '''
			AND [Depth] - (SELECT [Depth]
				FROM medianamik_node
				WHERE [NodeId] = ''' + @NodeId + ''')  <=  ' + @MaxDepth + '
			AND [CultureName] = ''' + @CultureName + '''
			AND [Generation] = ' + @Generation +
			@IsActiveSql +
			'ORDER BY [Depth] ASC, [Position] ASC, [CreatedOn] Desc')
		END
END
